package com.hongdezhi.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.hongdezhi.domain.*;
import com.hongdezhi.utils.C3P0Utils;

public class NoticeDao {
		// 添加公告的操作 insert
		public boolean insert(Notice notice) throws SQLException {	
			
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "insert into notice_inf(title,content) values(?,?)";
			int i = runner.update(sql, new Object[]{notice.getTitle(),notice.getContent()});
			if(i>0)
				return true;
			return false;
		}
		// 查询所有的Notice对象 query
		public List<Notice> findAll() throws SQLException {
			
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "select * FROM notice_inf";
//			String sql = "select ID,TITLE,CONTENT,CREATE_DATE,USER_ID FROM notice_inf";
			List<Notice> list = runner.query(sql, new BeanListHandler<Notice>(Notice.class));
			if(list!=null)
				return list;
			return null;
			
		}
		// 根据id查找指定的Notice query 模糊查询
		public Notice find(int id) throws SQLException {
			
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "select id,title,content,createdate,userid from notice_inf WHERE id=?";
			Notice notice = runner.query(sql, new BeanHandler<Notice>(Notice.class), id);
			if(notice!=null)
				return notice;
			
			return null;
		}
		
		// 根据TITLE查找指定的Notice query 模糊查询
		public List findT(String title) throws SQLException {
					
					QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
					String sql = "select ID,TITLE,CONTENT,createdate,userid FROM notice_inf WHERE TITLE like ?";
					List<Notice> notice = runner.query(sql, new BeanListHandler<Notice>(Notice.class), "%"+title+"%");
					if(notice.size()>0)
						return notice;
					
					return null;
		}
		
		// 根据CONTENT查找指定的Notice query 模糊查询
		public List findC(String title) throws SQLException {
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "select ID,TITLE,CONTENT,createdate,userid FROM notice_inf WHERE CONTENT like ?";
			List<Notice> notice = runner.query(sql, new BeanListHandler<Notice>(Notice.class), "%"+title+"%");
			if(notice.size()>0)
				return notice;
							
			return null;
		}
		
		// 根据CREATE_DATE查找指定的Notice query 模糊查询
		public List findD(String title) throws SQLException {
					QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
					String sql = "select ID,TITLE,CONTENT,createdate,userid FROM notice_inf WHERE createdate like ?";
					List<Notice> notice = runner.query(sql, new BeanListHandler<Notice>(Notice.class), "%"+title+"%");
					if(notice.size()>0)
						return notice;
									
					return null;
		}
		
		// 根据USER_ID查找指定的Notice query 模糊查询
		public List findU(String title) throws SQLException {
			Integer i = Integer.parseInt(title);
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "select ID,TITLE,CONTENT,createdate,userid FROM notice_inf WHERE userid like ?";
			List<Notice> notice = runner.query(sql, new BeanListHandler<Notice>(Notice.class), "%"+i+"%");
			if(notice!=null)
				return notice;
											
			return null;
		}
		
		// 删除用户 update
		public boolean delete(int id) throws SQLException {
			
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "DELETE FROM notice_inf WHERE ID=?";
			int i = runner.update(sql, new Object[]{id});
			if(i>0)
				return true;
			
			
			return false;
		}
		// 修改用户 update
		public boolean update(Notice notice) throws SQLException {
			
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "UPDATE notice_inf set title=?,content=?,createdate=?,userid=? WHERE id=?";
			int i = runner.update(sql, 
					new Object[]{notice.getTitle(),notice.getContent(),notice.getCreatedate(),notice.getUserid(),notice.getId()});
			if(i>0)
				return true;
			
			return false;
			
		}
	}

